<%@page contentType="text/html" pageEncoding="UTF-8" import="java.io.*, java.sql.*, javax.sql.*, java.util.*,org.apache.pdfbox.pdfparser.PDFParser, 
        javax.mail.*, javax.mail.internet.*, org.apache.commons.io.IOUtils,
        org.apache.pdfbox.util.PDFTextStripper, com.elookinto.wordlist.*, com.meterware.httpunit.*"
        %>

<%@page import="com.elookinto.wordlist.PDFExtractor"%>


<%
    request.setCharacterEncoding("UTF-8");
    String sql = request.getParameter("sql");
    if (sql != null) {
        sql = sql.trim();
    }
    if (sql != null && sql.equalsIgnoreCase("batch")) {
        new Thread() {
            public void run() {
                try {
                    parseTOEFL(null);
                    //parseSatII(null);
                    //parseSat(null);
                    //PDFExtractor.main(null);
                    //main(null);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }.start();
        return;
    }
%>

<form method="post"  accept-charset="UTF-8">
    <textarea rows=10 cols=120 name="sql">
        <%= sql%>
    </textarea>
    <input type="submit" />
</form>
<%
    Connection con = null;
    if (sql != null) {

        try {

            con = con = Util.getCon();;
            con.getMetaData();
            if (sql.indexOf("select") >= 0) {
                ResultSet rs = con.createStatement().executeQuery(sql);
                ResultSetMetaData metaData = rs.getMetaData();

                int rowCount = metaData.getColumnCount();

                for (int i = 0; i < rowCount; i++) {

                    out.print(metaData.getColumnName(i + 1) + " \t");
                    out.print(metaData.getColumnDisplaySize(i + 1) + "\t");
                    out.println(metaData.getColumnTypeName(i + 1));
                    out.println("");
                }

                out.println("<Table> ");
                while (rs.next()) {
                    out.println("<tr>");
                    for (int i = 0; i < rowCount; i++) {
                        out.println("<td>" + rs.getObject(i + 1) + "</td>");
                    }
                    out.println("</tr>");
                }
                out.println("</Table> ");
            } else {
                con.createStatement().executeUpdate(sql);
            }
            con.close();
        } catch (SQLException e) {
            out.println(e.getMessage());
        }
    }
    String op = request.getParameter("op");
    if ("testingmom".equals(op)) {
        testingmon(out);

    }

%>

<%!
    public static void parseTOEFL(String s) throws Exception {

        WebConversation wc = new WebConversation();
        //wc.setProxyServer("proxy.entp.attws.com", 8080, "itservices\\zw251y", "li196614%");
        HttpUnitOptions.setExceptionsThrownOnScriptError(false);

        WebRequest req = new GetMethodWebRequest("http://supervoca.net/toefllist.cgi");

        WebResponse resp = wc.getResponse(req);
        WebTable[] tables = resp.getTables();
        WebTable t = tables[0];

        for (int i = 0; i < t.getRowCount(); i++) {
            for (int j = 0; j < 5; j++) {
                int p = 1 + (i + j) % 20;
                String listName = "TOEFL_" + (p < 10 ? "0" + p : "" + p);
                insert_words(t.getCellAsText(i, j), listName);
            }
        }


    }

    public static void parseGRE(String s) throws Exception {

        WebConversation wc = new WebConversation();
        // wc.setProxyServer("proxy.entp.attws.com", 8080, "itservices\\zw251y", "li196614%");
        HttpUnitOptions.setExceptionsThrownOnScriptError(false);
        for (int k = 1; k <= 15; k++) {
            String sk = k < 10 ? "0" + k : "" + k;
            WebRequest req = new GetMethodWebRequest("http://www.majortests.com/gre/wordlist_" + sk);
            String listName = "GRE_" + sk;
            WebResponse resp = wc.getResponse(req);
            WebTable[] tables = resp.getTables();
            for (int j = 0; j < tables.length; j++) {
                WebTable t = tables[j];
                for (int i = 0; i < t.getRowCount(); i++) {
                    insert_words(t.getCellAsText(i, 0), listName, t.getCellAsText(i, 1));
                }

            }
        }
    }

    public static void parseSatII(String s) throws Exception {

        WebConversation wc = new WebConversation();
        // wc.setProxyServer("proxy.entp.attws.com", 8080, "itservices\\zw251y", "li196614%");
        HttpUnitOptions.setExceptionsThrownOnScriptError(false);
        for (int k = 16; k <= 25; k++) {
            WebRequest req = new GetMethodWebRequest("http://www.majortests.com/sat/wordlist-" + k);
            String listName = "SATOfficial_" + (k - 15 < 10 ? "0" : "") + (k - 15);
            WebResponse resp = wc.getResponse(req);
            WebTable[] tables = resp.getTables();
            if (tables.length > 0) {
                WebTable t = tables[0];
                for (int i = 0; i < t.getRowCount(); i++) {
                    insert_words(t.getCellAsText(i, 0), listName, t.getCellAsText(i, 1));
                }

            }
        }
    }

    public static void parseSat(String s) throws Exception {
        s = "/Users/zw251y/Documents/NetBeansProjects/wordlist/data/sat/word-list-01.pdf";
        File files = new File("/Users/zw251y/Documents/NetBeansProjects/wordlist/data/sat");
        int i = 0;
        String listNamePrefix = "SAT_Group";
        for (File file : files.listFiles()) {
            if (!file.getAbsolutePath().contains("pdf")) {
                continue;
            }
            System.out.println("Group" + i);
            i++;
            PDFParser pdf = new PDFParser(new FileInputStream(file));
            pdf.parse();
            PDFTextStripper ps = new PDFTextStripper();
            String text = ps.getText(pdf.getPDDocument());
            pdf.getPDDocument().close();
            String listName = "";
            String lines[] = text.split("\n");

            for (String line : lines) {
                if (line.contains("Group")) {
                    listName = listNamePrefix + (3 + i) / 3;
                    i++;
                    continue;
                }
                if (line.contains("..............")) {
                    // System.out.println(line.substring(0, line.indexOf(' ')));
                    String word = line.substring(0, line.indexOf(' '));
                    System.out.println(word + "  " + listName);
                    insert_words(word, listName);
                }
            }
        }
    }

    public static void main(String s[]) throws Exception {
        grade1("c:/temp/pdfs/vocab1.pdf", 1);
        grade1("c:/temp/pdfs/vocab3.pdf", 3);
        grade1("c:/temp/pdfs/vocab4.pdf", 4);
        grade1("c:/temp/pdfs/vocab5.pdf", 5);
        grade6("c:/temp/pdfs/vocab6.pdf");
    }

    public static void grade1(String s, int grade) throws Exception {
        PDFParser pdf = new PDFParser(new FileInputStream(s));
        pdf.parse();
        PDFTextStripper ps = new PDFTextStripper();
        String text = ps.getText(pdf.getPDDocument());
        pdf.getPDDocument().close();
        //System.out.println(text);
        int beginWeek = 0;
        int week = 1;
        while (true) {
            String sweek = week < 10 ? "0" + week : "" + week;
            String listName = "Grade" + grade + "_" + "week" + sweek;
            System.out.println(listName);
            beginWeek = text.indexOf("Week " + week, beginWeek);
            if (beginWeek == -1) {
                break;
            }
            int one = text.indexOf("1.", beginWeek);
            //int six = text.indexOf("6.", one);
            int endWeek = text.indexOf("Week", one);
            if (endWeek == -1) {
                endWeek = text.length();
            }
            String weekWords = (text.substring(one, endWeek));
            //System.out.println(weekWords);
            insert_words(weekWords, listName);
            week++;
            beginWeek = endWeek;
        }

    }

    public static void grade6(String s) throws Exception {
        PDFParser pdf = new PDFParser(new FileInputStream(s));
        pdf.parse();
        PDFTextStripper ps = new PDFTextStripper();
        String text = ps.getText(pdf.getPDDocument());
        pdf.getPDDocument().close();
        //System.out.println(text);
        int beginWeek = 0;
        int week = 1;
        while (true) {
            String sweek = week < 10 ? "0" + week : "" + week;
            String listName = "Grade6_" + "week" + sweek;
            System.out.println(listName);
            beginWeek = text.indexOf("Week " + week, beginWeek);
            if (beginWeek == -1) {
                break;
            }
            int one = text.indexOf("THURSDAY", beginWeek) + 8;
            one = text.indexOf("\n", one);
            one = text.indexOf("\n", one + 2);
            int endWeek = text.indexOf("Week " + (week + 1), one);
            if (endWeek == -1) {
                endWeek = text.length();
            }
            String weekWords = (text.substring(one, endWeek));
            System.out.println(weekWords);
            insert_words(weekWords, listName);
            //System.out.println(weekWords);
            week++;
            beginWeek = endWeek;
        }

    }
    static HashSet hs = new HashSet() {
        {
            this.add("w");
            this.add("d");
            this.add("z");
            this.add("ee");
            this.add("comments");
            this.add("e");
            this.add("wd");
        }
    };

    public static void insert_words(String text, String listName) throws Exception {
        insert_words(text, listName, null);
    }

    public static void insert_words(String text, String listName, String def) throws Exception {
        String[] words = text.split("\\W+|\\d+");
        //HashSet set = new
        int count = 0;
        Connection con = Util.getCon();

        PreparedStatement st = con.prepareStatement("insert into wordlist (word, listname, uid, insert_time, def) values(?,?,?, current_timestamp, ?) ");
        for (String word : words) {
            word = word.toLowerCase();
            if (word.trim().length() == 0 || hs.contains(word)) {
                continue;
            }
            st.setString(1, word);
            st.setString(2, listName);
            st.setString(3, "admin");
            st.setString(4, def);
            try {
                st.execute();
            } catch (Exception e) {
                System.out.println(word + "_" + listName + " " + e);
                //e.printStackTrace();
            }
            count++;
        }
        st.close();
        con.close();
        System.out.println("count=" + count);
    }

    public static void testingmon(javax.servlet.jsp.JspWriter out) {

        // create table testingmom (d date primary key, subject varchar(150), text varchar(100000))
        Properties props = System.getProperties();
        String host1 = "imap.mail.yahoo.com"; //host;//args[0];
        String username1 = "zwu_ca@yahoo.com"; //username; //args[1];
        String password1 = "liliping01"; // args[2];
        //String provider  = "pop3";
        String provider = "imaps";

        try {
            Connection con = Util.getCon();

            PreparedStatement st = con.prepareStatement("insert into testingmom (d, subject, text) values (?,?,?) ");
            props.setProperty("mail.store.protocol", "imaps");
            //Connect to the server
            Session session = Session.getDefaultInstance(props, null);
            Store store = session.getStore(provider);
            store.connect(host1, username1, password1);

            //open the inbox folder
            Folder inbox = store.getFolder("testingmom");
            inbox.open(Folder.READ_ONLY);

            // get a list of javamail messages as an array of messages
            Message[] messages = inbox.getMessages();



            for (int i = 0; i <  messages.length; i++) {
                String subj = messages[i].getSubject();
                java.util.Date d = messages[i].getReceivedDate();
                String message = IOUtils.toString(messages[i].getInputStream(), "UTF-8");
                if (subj.contains("Question")) {
                    System.out.println(subj + "...." + d);
                    int b = message.indexOf("<html dir");
                    String SS = "The Testing Mom</span>";
                    b = message.indexOf(SS);
                    int e = message.indexOf("</body>", b);
                    if (b > 0 && e > 0) {
                        String qs = ("<html dir>" + message.substring(b + SS.length(), e + 7) + "</body></html>");
                        qs = (qs.replace("TestingMom.com", qs.length() + ""));
                        java.sql.Date sqlD = new java.sql.Date(d.getTime());
                        st.setDate(1, sqlD);
                        st.setString(2, subj);
                        st.setString(3, qs);
                        try {
                            st.executeUpdate();
                        } catch (Exception ex) {
                            ex.printStackTrace();
                        }
                        System.out.println(qs);
                    }
                }

            }


            //close the inbox folder but do not
            //remove the messages from the server
            inbox.close(false);
            store.close();
            con.close();
        } catch (NoSuchProviderException nspe) {
            System.err.println("invalid provider name");
        } catch (MessagingException me) {
            System.err.println("messaging exception");
            me.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
%>

